package com.dingding.order.core.confirm.sql;

/**
 * 
 * @ClassName: ConfirmSQL 
 * @Description: 确认到达操作SQL
 * @author CHEHAUNBO 
 * @date 2016年6月15日 下午9:48:43
 * 
 */
public class ConfirmSQL {

	/**
	 * 确认到达推送消息查询SQL
	 * 
	 * @author CHEHUANBO
	 * 
	 */
	public static final String PASSENGER_PAY_PUSH = "SELECT u.aliasId,u.loginEquipment,ud.tellphone,udp.nickName,ud.nickName AS 'driverName',v.lisencePlate FROM userlogin u INNER JOIN userdetail ud ON u.uid = ud.loginId INNER JOIN userdetail udp ON udp.loginId = ? LEFT JOIN vehicleinfo v ON u.uid = v.loginId WHERE u.uid = ?";

	/**
	 * 
	 * 根据用户ID删除订单
	 * 
	 * @author YHQ
	 */
	public static final String DELETE_ORDER_BY_ID = "DELETE FROM userordert WHERE oid = ?";
	
	/**
	 * 
	 * 获取自动确认到达订单列表
	 * 
	 * @author YHQ
	 * 
	 */
	public static final String AUTO_CONFIRM_ARRIVAL_ORDER_LIST = "SELECT 	oid, placeOrderUserId,payCity,setOutTime,startAddress,startLongitude,startLatitude,endAddress,endLongitude,endLatitude,distance,payMoney,alipayMoney,payType,ucId,couponMoney,totalMoney, number,redBag,message,payDistance,marker,grabOrderUserId,grabOrderTime,orderStatus,createTime,payTime,endTime,cancelTime,orderType,cancelType,cancelNumber,cancelDesc,isLongDistance,psgRcvIntegral,driverRcvIntegral,cancelUserId,isComment,isCarShare,setOutCondition,freeSeat,parOrderId,isChildren,lastDate FROM Userordert WHERE (TIMESTAMPDIFF(SECOND,DATE_ADD(setOutTime,INTERVAL 2 DAY_HOUR),NOW()) >= 0 AND orderStatus = 3)";
	
	/**
	 * 
	 * 自动确认到达发送短信、推送消息内容
	 * 
	 * @author YHQ
	 * 
	 */
	public static final String AUTO_CONFIRM_ARRIVAL_LIST = "SELECT u.oid, u.orderStatus, u.orderType, passengerul.uid AS 'passengerId', passengerul.loginEquipment AS 'passengerEquipment', passengerudl.nickName      AS 'passengerName', passengerudl.tellphone     AS 'passengerPhone', driverul.uid               AS 'driverId', driverul.loginEquipment    AS 'driverEquipment', driverudl.nickName         AS 'driverName', driverudl.tellphone        AS 'driverPhone', u.totalMoney, passengerul.aliasId        AS 'passengerAliasId', driverul.aliasId           AS 'driverAliasId', u.distance, u.alipayMoney, u.payMoney, u.couponMoney FROM userordert u LEFT JOIN userlogin passengerul ON passengerul.uid = ? LEFT JOIN userdetail passengerudl ON passengerudl.loginId = passengerul.uid LEFT JOIN userlogin driverul ON driverul.uid = ? LEFT JOIN userdetail driverudl ON driverul.uid = driverudl.loginId";
	
	/**
	 * 
	 * 订单完成后，如果用户参加活动有可能赠送积分。
	 * 如果赠送积分需要将获赠积分数更新到订单表中，当前sql由此产生
	 * @author CHEHUANBO
	 * 
	 */
	public static final String UPDATE_CURRENTORDER_RECEIVE_INTEGRAL = "UPDATE userorder uo set uo.driverRcvIntegral = ? where uo.oid = ?";
	
	/**
	 * 根据订单id查询taxi订单详情
	 * @author hufan
	 */
	//public static final String GET_USERTAXIORDERT_BY_ID = "SELECT 	taxiOrdertId,placeOrderUserId,placeOrderCity,setOutTime,startAddress,startLongitude,startLatitude,endAddress,endLongitude,endLatitude,distance,payMoney,alipayMoney,totalMoney,number,message,payDistance,grabOrderUserId,grabOrderTime,orderStatus,createTime,payTime,endTime,cancelTime,orderType,payType,cancelType,cancelDesc,isComment,cancelUserId,transactionType FROM UserTaxiOrdert WHERE taxiOrdertId=?";
	
}
